﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Elderos.Utils.SQL
{
    public class SqlQuery
    {
        public SqlTransaction Transaction { get; private set; }
        public string ConnectionString { get; private set; }

        public SqlQuery(string connectionString, SqlTransaction transaction = null)
        {
            ConnectionString = connectionString;
            Transaction = transaction;
        }

        public List<T> ExecuteList<T>(SqlCommand cmd, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            var result = new List<T>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;
                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(readerFunc(reader));
                    }
                }
            }
            return result;
        }

        public List<T> ExecuteList<T>(string commandText, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            return ExecuteList(new SqlCommand(commandText), readerFunc, parameters);
        }

        public async Task<List<T>> ExecuteListAsync<T>(SqlCommand cmd, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            var result = new List<T>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;

                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        result.Add(readerFunc(reader));
                    }
                }
            }
            return result;
        }

        public async Task<List<T>> ExecuteListAsync<T>(string commandText, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            return await ExecuteListAsync(new SqlCommand(commandText), readerFunc, parameters);
        }

        public T ExecuteSingle<T>(SqlCommand cmd, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;
                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();

                    return readerFunc(reader);
                }
            }
        }

        public T ExecuteSingle<T>(string commandText, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            return ExecuteSingle(new SqlCommand(commandText), readerFunc, parameters);
        }

        public async Task<T> ExecuteSingleAsync<T>(SqlCommand cmd, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;
                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    reader.Read();

                    return readerFunc(reader);
                }
            }
        }

        public async Task<T> ExecuteSingleAsync<T>(string commandText, Func<IDataReader, T> readerFunc, params SqlParameter[] parameters)
        {
            return await ExecuteSingleAsync(new SqlCommand(commandText), readerFunc, parameters);
        }

        public void ExecuteCmd(SqlCommand cmd, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;
                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                cmd.ExecuteNonQuery();
            }
        }

        public void ExecuteCmd(string commandText, params SqlParameter[] parameters)
        {
            ExecuteCmd(new SqlCommand(commandText), parameters);
        }

        public async Task ExecuteCmdAsync(SqlCommand cmd, params SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                cmd.Connection = connection;
                if (Transaction != null)
                    cmd.Transaction = Transaction;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                connection.Open();

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task ExecuteCmdAsync(string commandText, params SqlParameter[] parameters)
        {
            await ExecuteCmdAsync(new SqlCommand(commandText), parameters);
        }
    }
}
